﻿Imports System.Data.Linq
Imports System.Linq

Public Class MAdvanceVoucher
    Private db As New TanNamChinhDataContext

#Region "Sy's codes"
    'Thanh thêm vào ngày 23/10/2013
    'Hàm lấy thông tin AdvanceVoucher theo ID
    Public Function SelectRecordById(ByVal id As Integer) As tblAdvanceVoucher
        Try
            Return (From pv In db.tblAdvanceVouchers
                    Where pv.ID_AdvanceVoucher = id
                    Select pv).Single()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
#End Region
    ''' <summary>
    ''' Hàm lấy danh sách các phiếu tạm ứng quá hai ngày chưa có hoá đơn
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetVoucherList() As IQueryable
        Dim query
        'Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(2, 0, 0, 0, 0))
        Try
            query = From b In db.tblAdvanceVouchers
                    Order By b.AdvanceVoucherNo Descending
                    Select
                    b.ID_AdvanceVoucher,
                    b.CreatedDate,
                    b.AdvanceVoucherNo,
                    b.Amount,
                    b.ID_AdvanceUser,
                    AdvanceUser = If(b.tblUser1 IsNot Nothing, b.tblUser1.UserName, String.Empty),
                    b.ID_User,
                    CreatedUser = If(b.tblUser IsNot Nothing, b.tblUser.UserName, String.Empty),
                    b.Description,
                    ReceiptVoucherNo = If(b.tblReceiptVoucher IsNot Nothing, b.tblReceiptVoucher.ReceiptVoucherNo, String.Empty),
                    ApproveUser = If(b.tblUser2 IsNot Nothing, b.tblUser2.FullName, String.Empty),
                    b.IsApprove
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetVoucherListSearch(ByVal idFile As Integer) As IQueryable
        Dim query
        'Lấy danh sách các phiếu tạm ứng của file
        Dim q = (From b In db.tblFileFees
                Where b.ID_File = idFile
                Select b.ID_AdvanceVoucher).Distinct

        'Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(2, 0, 0, 0, 0))
        Try
            query = From b In db.tblAdvanceVouchers
                    Where q.Contains(b.ID_AdvanceVoucher)
                    Order By b.AdvanceVoucherNo Descending
                    Select
                    b.ID_AdvanceVoucher,
                    b.CreatedDate,
                    b.AdvanceVoucherNo,
                    b.Amount,
                    b.ID_AdvanceUser,
                    AdvanceUser = If(b.tblUser1 IsNot Nothing, b.tblUser1.UserName, String.Empty),
                    b.ID_User,
                    CreatedUser = If(b.tblUser IsNot Nothing, b.tblUser.UserName, String.Empty),
                    b.Description,
                    ReceiptVoucherNo = If(b.tblReceiptVoucher IsNot Nothing, b.tblReceiptVoucher.ReceiptVoucherNo, String.Empty),
                    ApproveUser = If(b.tblUser2 IsNot Nothing, b.tblUser2.FullName, String.Empty),
                    b.IsApprove
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetVoucherList(ByVal dataAdvance As DateTime) As IQueryable
        Dim query
        'Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(2, 0, 0, 0, 0))
        Try
            query = From b In db.tblAdvanceVouchers
                    Where b.CreatedDate = dataAdvance.ToShortDateString()
                    Order By b.CreatedDate Descending
                    Select
                    b.ID_AdvanceVoucher,
                    b.CreatedDate,
                    b.AdvanceVoucherNo,
                    b.Amount,
                    b.ID_AdvanceUser,
                    Nguoi_Tam_Ung = If(b.tblUser1 IsNot Nothing, b.tblUser1.UserName, String.Empty),
                    b.ID_User,
                    Nguoi_Lap_Phieu = If(b.tblUser IsNot Nothing, b.tblUser.UserName, String.Empty),
                    b.IsApprove,
                    b.Description,
                    ReceiptVoucherNo = If(b.tblReceiptVoucher IsNot Nothing, b.tblReceiptVoucher.ReceiptVoucherNo, String.Empty)

            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function GetVoucher(ByVal idAdvance As Integer) As IQueryable
        Dim query
        'Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(2, 0, 0, 0, 0))
        Try
            query = From b In db.tblAdvanceVouchers
                    Where b.ID_AdvanceVoucher = idAdvance
                    Select
                    b
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub InsertNewRecord(ByVal obj As tblAdvanceVoucher)
        'db.tblOilPayments.InsertOnSubmit(obj)
        db.GetTable(Of tblAdvanceVoucher)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub
    Public Sub UpdateRecord(ByVal obj As tblAdvanceVoucher)
        Dim getData = (From b In db.tblAdvanceVouchers
                                Where b.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
                                Select b).Single()
        'getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
 

        getData.ID_User = obj.ID_User
        getData.CreatedDate = obj.CreatedDate
        getData.AdvanceVoucherNo = obj.AdvanceVoucherNo
        getData.Description = obj.Description
        getData.ID_AdvanceUser = obj.ID_AdvanceUser
        getData.Amount = obj.Amount
        getData.IsApprove = obj.IsApprove
       
        db.SubmitChanges()
        'End If



    End Sub
    Public Sub UpdatePrice(ByVal obj As tblAdvanceVoucher)
        Dim getData = (From b In db.tblAdvanceVouchers
                                Where b.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
                                Select b).Single()
        'getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.Amount = obj.Amount

        db.SubmitChanges()
        'End If



    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Try
            Dim getData = (From b In db.tblAdvanceVouchers
                            Where b.ID_AdvanceVoucher = id
                            Select b).Single()
            db.tblAdvanceVouchers.DeleteOnSubmit(getData)
            db.SubmitChanges()
        Catch ex As Exception
            Throw ex
        End Try
    
    End Sub

    Public Function GetPrice(ByVal id As Integer) As Double
        Dim prices As Double
        prices = (From b In db.tblAdvanceVouchers
                 Where b.ID_AdvanceVoucher = id
                 Select b.Amount).First()
        Return prices
    End Function
    Public Function GetPriceOfFileFee(ByVal idAdvance As Integer, ByVal idFile As Integer, ByVal idFee As Integer) As Double
        Dim prices As Double
        prices = (From b In db.tblFileFees
                 Where b.ID_AdvanceVoucher = idAdvance And b.ID_File = idFile And b.ID_Fee = idFee
                 Select b.Price).First()
        Return prices
    End Function
    Public Function getIDNewAdvanceVoucher() As Integer
        Dim id_pv As Integer
        Try
            id_pv = (From b In db.tblAdvanceVouchers
                     Order By b.ID_AdvanceVoucher Descending
                        Select b.ID_AdvanceVoucher
                       ).First()
        Catch ex As Exception
            Throw ex
        End Try
        Return id_pv
    End Function
    ''' <summary>
    ''' Kiểm tra xem phiếu tạm ứng này của file hay của cont
    ''' Nếu của file trả về true, của cont trả về false
    ''' </summary>
    ''' <param name="idAdvance"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsInFileOrCont(ByVal idAdvance As Integer) As Boolean
        Dim query = From a In db.tblFileFees
                    Where a.ID_AdvanceVoucher = idAdvance
                    Select a

        If query.Count > 0 Then
            'Có tồn tại trong filefee
            Return True
        Else
            Return False
        End If

    End Function
    Public Function GetListFileOrContOfAdvance(ByVal idAdvance As Integer) As IQueryable
        Dim query
        'Nếu là file


        'Trả về danh sách file
        query = (From a In db.tblFileFees
                 Where a.ID_AdvanceVoucher = idAdvance
                 Select a.ID_File, a.tblFile.FileNo).Distinct()


        Return query
    End Function
    Public Function GetIDFileOfAdvance(ByVal idAdvance As Integer) As Integer
        Dim query
        'Nếu là file
        Try
            query = (From a In db.tblFileFees
                Where a.ID_AdvanceVoucher = idAdvance
                Select a.ID_File).First()

            Return query
        Catch ex As Exception
            Throw ex
        End Try

       
    End Function
    Public Function GetFileNoOfAdvance(ByVal idAdvance As Integer) As Integer
        Dim query
        'Nếu là file


        query = (From a In db.tblFileFees
                 Where a.ID_AdvanceVoucher = idAdvance
                 Select a.tblFile.FileNo).First()

        Return query
    End Function
    Public Function GetTypeFileOfAdvance(ByVal idAdvance As Integer) As Integer
        Dim query
        'Nếu là file


        query = (From a In db.tblFileFees
                 Where a.ID_AdvanceVoucher = idAdvance
                 Select a.tblFile.IsExport).First()

        Return query
    End Function
    Public Function GetListDetailFee(ByVal idAdvance As Integer) As IQueryable
        Dim query


        query = From a In db.tblFileFees
                Where a.ID_AdvanceVoucher = idAdvance
                Select New With {
         a.ID_FileFee,
        a.ID_File,
        a.ID_Fee,
        .Fee = If(a.tblFee IsNot Nothing, a.tblFee.FeeName, String.Empty),
        a.Price
        }
        Return query

    End Function
    Public Function GetListDetailFee(ByVal idAdvance As Integer, ByVal isfile As Boolean) As IQueryable
        Dim query

        
                'Nếu là của file
                query = From a In db.tblFileFees
                        Where a.ID_AdvanceVoucher = idAdvance
                        Select
                        a.ID_FileFee,
                        a.ID_File,
                        FileNo = If(a.tblFile IsNot Nothing, a.tblFile.FileNo, String.Empty),
                        a.ID_Fee,
                        Fee = If(a.tblFee IsNot Nothing, a.tblFee.FeeName, String.Empty),
                        a.Price
        
        Return query

    End Function
    ''' <summary>
    ''' Trả về danh sách các file chưa nộp hóa đơn sau số ngày chậm
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetListFileSlow(ByVal days As Integer) As IQueryable
        'Lấy danh sách các phiếu tạm ứng cách đây lớn hơn days ngày
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(days, 0, 0, 0, 0))
        Dim listAd = From a In db.tblAdvanceVouchers
                     Where a.CreatedDate > oldestDate
                     Select a.ID_AdvanceVoucher

        'Quét danh sách này và lấy ra file
        Dim listFile = (From b In db.tblFileFees
                       Where listAd.Contains(b.ID_AdvanceVoucher)
                       Select b.ID_File).Distinct().ToList()
        'Lấy thông tin các file này và hiển thị ra
        Dim file = From c In db.tblFiles
                   Where listFile.Contains(c.ID_File)
                   Select c.ID_File, c.FileNo, c.CreatedDate,
                   CustomerName = If(c.tblCustomer IsNot Nothing, c.tblCustomer.CustomerName, String.Empty),
                    Company = If(c.tblCompany IsNot Nothing, c.tblCompany.Name, String.Empty)


    End Function
    Public Function GetListAdvanceSlow(ByVal days As Integer) As IQueryable
        'Lấy danh sách các phiếu tạm ứng cách đây lớn hơn days ngày
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(days, 0, 0, 0, 0))
        Dim listAd = From a In db.tblAdvanceVouchers
                     Where a.CreatedDate > oldestDate
                     Select a.ID_AdvanceVoucher

        Return listAd
    End Function
    Public Function GetListDetailAdvanceSlow(ByVal days As Integer)
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(days, 0, 0, 0, 0))
        'Danh sách các phiếu tạm ứng bị chậm
        Dim listAd = From a In db.tblAdvanceVouchers
                     Where a.CreatedDate < oldestDate
                     Select a.ID_AdvanceVoucher
        ' Danh sách các phiếu tạm ứng đến hạn và chưa được thanh toán(phần phiếu thu bị NULL)
        ' Sau khi tạm ứng thì sau số ngày quy định phải đem hóa đơn về và 
        ' thu lại số tiền tạm ứng, nhập phiếu chi cho phiếu tạm ứng này
        Dim listDAd = From b In db.tblAdvanceVouchers
                      Where b.CreatedDate < oldestDate And Not b.ID_ReceiptVoucher.HasValue
                      Order By b.CreatedDate Ascending
                      Select New With {b.ID_AdvanceVoucher,
                      b.AdvanceVoucherNo,
                      b.CreatedDate,
                      .Today = DateTime.Now(),
                      .DelayDate = DateTime.Now().Subtract(b.CreatedDate).Days,
                     .Amount = b.Amount,
                      .AdvanceUser = If(b.tblUser1 IsNot Nothing, b.tblUser1.UserName, String.Empty),
                      .FileNo = "",
                    .IsExport = True}

 
       
        Return listDAd
    End Function
    ''' <summary>
    ''' Kiểm tra một phiếu tạm ứng có phải là bị chậm thanh toán hoàn ứng hay không
    ''' </summary>
    ''' <param name="days"></param>
    ''' <param name="idAdvance"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsAdvanceDelay(ByVal days As Integer, ByVal idAdvance As Integer) As Boolean
        Dim oldestDate As DateTime = DateTime.Now.Subtract(New TimeSpan(days, 0, 0, 0, 0))
        'Danh sách các phiếu tạm ứng bị chậm
        Dim listAd = From a In db.tblAdvanceVouchers
                     Where a.CreatedDate < oldestDate
                     Select a.ID_AdvanceVoucher
        ' Danh sách các phiếu tạm ứng đến hạn và chưa được thanh toán(phần phiếu thu bị NULL)
        ' Sau khi tạm ứng thì sau số ngày quy định phải đem hóa đơn về và 
        ' thu lại số tiền tạm ứng, nhập phiếu chi cho phiếu tạm ứng này
        Dim listDAd = From b In db.tblAdvanceVouchers
                      Where b.CreatedDate < oldestDate And Not b.ID_ReceiptVoucher.HasValue And b.ID_AdvanceVoucher = idAdvance
                      Order By b.CreatedDate Ascending
                      Select New With {b.ID_AdvanceVoucher,
                      b.AdvanceVoucherNo,
                      b.CreatedDate,
                      .Today = DateTime.Now(),
                      .DelayDate = DateTime.Now().Subtract(b.CreatedDate).Days,
                     .Amount = b.Amount,
                      .AdvanceUser = If(b.tblUser1 IsNot Nothing, b.tblUser1.UserName, String.Empty),
                      .FileNo = "",
                    .IsExport = True}


        If listDAd.Count > 0 Then
            'Bị chậm
            Return True
        Else
            Return False
        End If



    End Function
    Public Function GetListFileOrContOfAdvanceSlow(ByVal idAdvance As Integer) As IQueryable
        Dim query
        'Nếu là file


        'Trả về danh sách file
        query = (From a In db.tblFileFees
                 Where a.ID_AdvanceVoucher = idAdvance
                 Select a.tblFile.FileNo, a.tblFile.CreatedDate,
                  CustomerName = If(a.tblFile.tblCustomer IsNot Nothing, a.tblFile.tblCustomer.CustomerName, String.Empty)).Distinct()
        
        Return query
    End Function
    Public Function GetListFeeAdvance() As IQueryable(Of tblFee)
        Dim query = From a In db.tblFees
                    Where a.ID_FeeType = 1
                    Select a
        Return query
    End Function
    Public Function GetLastedAdvanceNo() As Integer
        Try
            Dim query As String = (From a In db.tblAdvanceVouchers
                               Order By a.ID_AdvanceVoucher Descending
                               Select a.AdvanceVoucherNo).First()


            Dim str() = query.Split("/")
            Return Integer.Parse(str(0))

        Catch ex As Exception
            Return 0
        End Try

    End Function
End Class
